﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

using GemBox.Spreadsheet;

namespace CheckList
{
    public partial class formKompletMain : Form
    {
        private string curentWyrob;
        private string curentKomplet;
        private string curentPaczka = null;
        private string curentGMat = "AKCES";
        private string curentElement = null;
        private DataSet akcesoriaDataSet = new DataSet();
        private OdbcDataAdapter elementyDataAdapter;
        private DataTable budowaDataTable;
        private classKartyTechniczne cKTech = new classKartyTechniczne();
        private DataSet dsElem = new DataSet();

        public formKompletMain(string [] wyrob)
        {
            curentWyrob = wyrob[0];
            
            InitializeComponent();
            this.nazwaProgramu.Text = wyrob[1];
            this.textBoxIdProgram.Text = curentWyrob;
            this.textBoxIdKomplet.Text = "";

            this.dataGridViewElementyVsKartyTech.CellEnter += new DataGridViewCellEventHandler(dataGridViewElementyVsKartyTech_CellEnter);
            this.dataGridViewBudowa.RowEnter += new System.Windows.Forms.DataGridViewCellEventHandler(dataGridViewBudowa_RowEnter);
        }

        private void formWyrobMain_Load(object sender, EventArgs e)
        {
            kompletyLoad();
            rodzajMaterialuLoad();
      
        }

        private void formWyrobMain_Activated(object sender, EventArgs e)
        {
           comboBox_GrupaMaterialowaLoad();
        }

        private void kompletyLoad()
        {
            DataSet kompletDataSet = new DataSet();
            try
            {
                // Creates and opens an ODBC connection
                string strConnString = classConfig.sqlConnString;
                OdbcConnection conn;
                conn = new OdbcConnection(strConnString.Trim());
                conn.Open();

                //Creates the data adapter
                OdbcDataAdapter wyrobyDataAdapter = new OdbcDataAdapter("SELECT * FROM komplet WHERE id_program = "+curentWyrob+" ORDER BY model", conn);
                //Fills dataset with the records from database 
                wyrobyDataAdapter.Fill(kompletDataSet, "komplet");

                conn.Close();

                // ustawienie kolumn
                this.listBoxKomplety.DataSource = kompletDataSet;
                this.listBoxKomplety.DisplayMember = "komplet.model";
                this.listBoxKomplety.ValueMember = "komplet.id_komplet";

            }
            catch (Exception e) //Error
            {
                MessageBox.Show(e.Message, "Error - Wczytywania listy kompletów", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }


        private void paczkiLoad()
        {
            DataSet paczkiDataSet = new DataSet();
            try
            {
                // Creates and opens an ODBC connection
                string strConnString = classConfig.sqlConnString;
                OdbcConnection conn;
                conn = new OdbcConnection(strConnString.Trim());
                conn.Open();

                // dodaje kolumny
                DataColumn id_paczka = new DataColumn("id_paczka", System.Type.GetType("System.String"));
                DataColumn nazwa_paczki = new DataColumn("nazwa_paczki", System.Type.GetType("System.String"));
                DataTable paczkiDataTable = paczkiDataSet.Tables.Add("paczka");
                paczkiDataTable.Columns.Add(id_paczka);
                paczkiDataTable.Columns.Add(nazwa_paczki);

                //dodaje paczke id_paczka = 0 dla ktorej wyswietLAM PACZKI razem
                DataRow newRow0 = paczkiDataSet.Tables["paczka"].NewRow();
                newRow0["id_paczka"] = 0; newRow0["nazwa_paczki"] = "RAZEM paczki";
                paczkiDataSet.Tables["paczka"].Rows.Add(newRow0);


                //Creates the data adapter
                OdbcDataAdapter paczkaDataAdapter = new OdbcDataAdapter("SELECT id_paczka,nazwa_paczki FROM paczka WHERE id_komplet = " + curentKomplet + " ORDER BY id_paczka", conn);
                paczkaDataAdapter.TableMappings.Add("Table", "paczka");
                //Fills dataset with the records from database 
                paczkaDataAdapter.Fill(paczkiDataSet, "paczka");

                //wprowadzam dane do combobox
                this.comboBoxPaczki.DataSource = paczkiDataSet.DefaultViewManager;
                this.comboBoxPaczki.DisplayMember = "paczka.nazwa_paczki";
                this.comboBoxPaczki.ValueMember = "paczka.id_paczka";

                //closes the connection
                conn.Close();

                this.comboBoxPaczki.SelectionChangeCommitted += new System.EventHandler(comboBoxPaczki_SelectionChangeCommitted);

            }
            catch (Exception e) //Error
            {
                MessageBox.Show(e.Message, "Error - Wczytywana lista paczek", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void rodzajMaterialuLoad()
        {
            DataSet rodzajMatDataSet = new DataSet();
            try
            {
                // Creates and opens an ODBC connection
                string strConnString = classConfig.sqlConnString;
                OdbcConnection conn;
                conn = new OdbcConnection(strConnString.Trim());
                conn.Open();

                //Creates the data adapter
                OdbcDataAdapter rodzajMatDataAdapter = new OdbcDataAdapter("SELECT material FROM elementy WHERE material != '' Group BY material", conn);
                rodzajMatDataAdapter.TableMappings.Add("Table", "rodzajMat");
                //Fills dataset with the records from database 
                rodzajMatDataAdapter.Fill(rodzajMatDataSet, "rodzajMat");

                //wprowadzam dane do combobox
                this.comboBoxMaterial.DataSource = rodzajMatDataSet.DefaultViewManager;
                this.comboBoxMaterial.DisplayMember = "rodzajMat.material";
                this.comboBoxMaterial.ValueMember = "rodzajMat.material";
                //closes the connection
                conn.Close();

            }
            catch (Exception e) //Error
            {
                MessageBox.Show(e.Message, "Error - Wczytywana lista rodzajow materialu", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void comboBox_GrupaMaterialowaLoad()
        {
            DataSet grupaMaterialowaDataSet = new DataSet();
            try
            {
                DataColumn gmID = new DataColumn("gmID", System.Type.GetType("System.String"));
                DataColumn gmName = new DataColumn("gmName", System.Type.GetType("System.String"));
                DataTable GrupaMaterialowa = grupaMaterialowaDataSet.Tables.Add("GrupaMaterialowa");
                GrupaMaterialowa.Columns.Add(gmID);
                GrupaMaterialowa.Columns.Add(gmName);

                DataRow newRow0 = GrupaMaterialowa.NewRow();
                newRow0["gmID"] = "AKCES"; newRow0["gmName"] = "Akcesoria"; GrupaMaterialowa.Rows.Add(newRow0);
                DataRow newRow1 =GrupaMaterialowa.NewRow();
                newRow1["gmID"] = "PWL"; newRow1["gmName"] = "Płyta wiórowa"; GrupaMaterialowa.Rows.Add(newRow1);
                DataRow newRow2 = GrupaMaterialowa.NewRow();
                newRow2["gmID"] = "HDF"; newRow2["gmName"] = "Płyta HDF"; GrupaMaterialowa.Rows.Add(newRow2);
                DataRow newRow3 = GrupaMaterialowa.NewRow();
                newRow3["gmID"] = "WSTEGA"; newRow3["gmName"] = "Wstęgi"; GrupaMaterialowa.Rows.Add(newRow3);
                DataRow newRow4 = GrupaMaterialowa.NewRow();
                newRow4["gmID"] = "KART"; newRow4["gmName"] = "Kartony"; GrupaMaterialowa.Rows.Add(newRow4);
                DataRow newRow5 = GrupaMaterialowa.NewRow();
                newRow5["gmID"] = "SZKLO"; newRow5["gmName"] = "Szkło"; GrupaMaterialowa.Rows.Add(newRow5);
                DataRow newRow6 = GrupaMaterialowa.NewRow();
                newRow6["gmID"] = "MDF"; newRow6["gmName"] = "Listwy MDF"; GrupaMaterialowa.Rows.Add(newRow6);

                DataRow newRowi = GrupaMaterialowa.NewRow();
                newRowi["gmID"] = "RAZEM"; newRowi["gmName"] = "Razem elementy"; GrupaMaterialowa.Rows.Add(newRowi);

                this.comboBoxGrupaMaterialowa.DataSource = grupaMaterialowaDataSet.DefaultViewManager;
                this.comboBoxGrupaMaterialowa.DisplayMember = "GrupaMaterialowa.gmName";
                this.comboBoxGrupaMaterialowa.ValueMember = "GrupaMaterialowa.gmID";


                this.comboBoxGrupaMaterialowa.SelectionChangeCommitted += new System.EventHandler(comboBoxGrupaMaterialowa_SelectionChangeCommitted);

            }
            catch (Exception e) //Error
            {
                MessageBox.Show(e.Message, "Error - Wczytywana lista paczek", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void kompletLoadBudowa()
        {
            akcesoriaDataSet = new DataSet();
            try
            {
                // Creates and opens an ODBC connection
                string strConnString = classConfig.sqlConnString;
                OdbcConnection conn;
                conn = new OdbcConnection(strConnString.Trim());
                conn.Open();

                //Creates the data adapter
                string sql = "";
                if (curentPaczka == null || curentPaczka == "0")
                {
                    if (curentGMat != "RAZEM")
                    {
                        sql = "SELECT a.*, b.*, b.cena_mat * a.ilosc_szt AS wartosc_mat FROM elementy AS a LEFT JOIN cennik AS b ON b.id_material = a.id_element WHERE a.id_komplet = " + curentKomplet + " AND a.grupa_mat = '" + curentGMat + "' ORDER BY a.element";
                    }
                    else
                    {
                        sql = "SELECT a.*, b.*, b.cena_mat * a.ilosc_szt AS wartosc_mat FROM elementy AS a LEFT JOIN cennik AS b ON b.id_material = a.id_element WHERE a.id_komplet = " + curentKomplet + " AND a.grupa_mat != 'AKCES' ORDER BY a.element";
                    }
                }
                else
                {
                    if (curentGMat != "RAZEM")
                    {
                        sql = "SELECT a.*, b.*, b.cena_mat * a.ilosc_szt AS wartosc_mat FROM elementy AS a LEFT JOIN cennik AS b ON b.id_material = a.id_element WHERE a.id_komplet = " + curentKomplet + " AND a.grupa_mat = '" + curentGMat + "' AND a.id_paczka = '" + curentPaczka + "' ORDER BY a.element";
                    }
                    else
                    {
                        sql = "SELECT a.*, b.*, b.cena_mat * a.ilosc_szt AS wartosc_mat FROM elementy AS a LEFT JOIN cennik AS b ON b.id_material = a.id_element WHERE a.id_komplet = " + curentKomplet + " AND a.grupa_mat != 'AKCES' AND a.id_paczka = '" + curentPaczka + "' ORDER BY a.element";
                    }
                }
                

              
                elementyDataAdapter = new OdbcDataAdapter(sql, conn);
                //Fills dataset with the records from database 
                elementyDataAdapter.Fill(akcesoriaDataSet, "elementy");


                // powiazanie pol z grid
                textBoxIlosc.DataBindings.Clear();
                textBoxIlosc.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "ilosc_szt"));
                textBoxRozmiary.DataBindings.Clear();
                textBoxRozmiary.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "abmessungen"));
                textBoxProducent.DataBindings.Clear();
                textBoxProducent.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "liferant"));
                textBoxElement.DataBindings.Clear();
                textBoxElement.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "element"));
                textBoxArtNumer.DataBindings.Clear();
                textBoxArtNumer.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "art_numer"));
                comboBoxMaterial.DataBindings.Clear();
                comboBoxMaterial.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "material"));

                textBoxDlugosc.DataBindings.Clear();
                textBoxDlugosc.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "dlugosc"));
                textBoxSzerokosc.DataBindings.Clear();
                textBoxSzerokosc.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "szerokosc"));

                textBoxCenaMat.DataBindings.Clear();
                textBoxCenaMat.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "cena_mat"));
                textBoxWartoscMat.DataBindings.Clear();
                textBoxWartoscMat.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "wartosc_mat"));

                textBoxIloscRysunkow.DataBindings.Clear();
                textBoxIloscRysunkow.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "il_rys"));
                textBoxIloscPaleta.DataBindings.Clear();
                textBoxIloscPaleta.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "il_paleta"));
                textBoxZnacznik.DataBindings.Clear();
                textBoxZnacznik.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "znacznik_korpus_mdf"));

                budowaDataTable = akcesoriaDataSet.Tables["elementy"];

                //closes the connection
                conn.Close();

                

                // budowa kolumn elementow, akcesorii itp
                kolumnyAkcesoria();


                this.dataGridViewBudowa.DataSource = budowaDataTable;
                

            }
            catch (Exception e) //Error
            {
                MessageBox.Show(e.Message, "Error - Wczytywania listy elementow", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void listBoxKomplety_Click(object sender, EventArgs e)
        {
            curentKomplet = listBoxKomplety.SelectedValue.ToString();
            this.textBoxIdKomplet.Text = curentKomplet;
            paczkiLoad();
            curentPaczka = this.comboBoxPaczki.SelectedValue.ToString();
            textBoxIdPaczki.Text = null;
           
            kompletLoadBudowa();
            

            loadKartyTechniczne();
            this.textBoxPoprawnoscKart.Text = cKTech.weryfikujKartyTechniczne(dsElem);
            // wyczysc datagrid z poprzedniego importu
            dataGridView_preView.DataSource = null;

            tabControlKartyTechniczne.Enabled = true;
            groupBoxPaczkiAndGrupaMat.Enabled = true;
        }

        private void dataGridViewBudowa_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
           curentElement = this.dataGridViewBudowa[0, e.RowIndex].Value.ToString();

           textBoxRozmiary.Enabled = (this.curentGMat == "AKCES") ? true : false;
           textBoxRozmiary.Visible = (this.curentGMat == "AKCES") ? true : false;

           textBoxProducent.Enabled = (this.curentGMat == "AKCES") ? true : false;
           textBoxProducent.Visible = (this.curentGMat == "AKCES") ? true : false;

           textBoxCenaMat.Enabled = (this.curentGMat == "AKCES") ? true : false;
           textBoxCenaMat.Visible = (this.curentGMat == "AKCES") ? true : false;
           textBoxWartoscMat.Enabled = (this.curentGMat == "AKCES") ? true : false;
           textBoxWartoscMat.Visible = (this.curentGMat == "AKCES") ? true : false;

           comboBoxMaterial.Enabled = (this.curentGMat == "AKCES") ? false : true;
           //comboBoxMaterial.SelectedIndex = comboBoxMaterial.FindString(this.dataGridViewBudowa[7, e.RowIndex].Value.ToString());

           textBoxDlugosc.Enabled = (this.curentGMat == "AKCES") ? false : true;
           textBoxDlugosc.Visible = (this.curentGMat == "AKCES") ? false : true;

           textBoxSzerokosc.Enabled = (this.curentGMat == "AKCES") ? false : true;
           textBoxSzerokosc.Visible = (this.curentGMat == "AKCES") ? false : true;

           label6.Visible = (this.curentGMat == "AKCES") ? true : false;
           label7.Visible = (this.curentGMat == "AKCES") ? true : false;

           label8.Visible = (this.curentGMat == "AKCES") ? true : false;
           label9.Visible = (this.curentGMat == "AKCES") ? true : false;

           label11.Visible = (this.curentGMat == "AKCES") ? false : true;
           label12.Visible = (this.curentGMat == "AKCES") ? false : true;

        }

   
        private void comboBoxPaczki_SelectionChangeCommitted(object sender, System.EventArgs e)
        {
            curentPaczka = this.comboBoxPaczki.SelectedValue.ToString();
            textBoxIdPaczki.Text = curentPaczka;
            kompletLoadBudowa();
            
        }


        private void comboBoxGrupaMaterialowa_SelectionChangeCommitted(object sender, System.EventArgs e)
        {
            curentGMat = this.comboBoxGrupaMaterialowa.SelectedValue.ToString();
            kompletLoadBudowa();
        }

        /// <summary>
        /// budowa kolumn dla listy kacesorii
        /// </summary>
        private void kolumnyAkcesoria()
        {

            // ustawienie kolumn
            this.dataGridViewBudowa.AutoGenerateColumns = false;

            if (this.dataGridViewBudowa.ColumnCount == 0)
            {
                DataGridViewTextBoxColumn IDe = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn IDk = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn ilosc_szt = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn cena_mat = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn wartosc_mat = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn Nazwa = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn Material = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn Numer = new DataGridViewTextBoxColumn();
 
                DataGridViewTextBoxColumn Wymiary = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn Producent = new DataGridViewTextBoxColumn();

                DataGridViewTextBoxColumn Dlugosc = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn Szerokosc = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn IloscRysunkow = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn IloscPaleta = new DataGridViewTextBoxColumn();
                DataGridViewTextBoxColumn Znacznik = new DataGridViewTextBoxColumn();
           
                IDe.DataPropertyName = "id_element";
                IDe.HeaderText = "Element";
                IDe.Width = 30;
                IDe.Visible = false;
                IDe.Name = "Element";

                
                IDk.DataPropertyName = "id_komplet";
                IDk.HeaderText = "Komplet";
                IDk.Visible = false;
                IDk.Width = 30;

                
                ilosc_szt.DataPropertyName = "ilosc_szt";
                ilosc_szt.HeaderText = "Ilośc";
                ilosc_szt.Visible = true;
                ilosc_szt.Width = 45;
                ilosc_szt.DefaultCellStyle.Format = "0.0";
                ilosc_szt.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

                cena_mat.DataPropertyName = "cena_mat";
                cena_mat.HeaderText = "Cena";
                cena_mat.Name = "CenaMat";
                cena_mat.Visible = true;
                cena_mat.Width = 45;
                cena_mat.DefaultCellStyle.Format = "0.0";
                cena_mat.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

                wartosc_mat.DataPropertyName = "wartosc_mat";
                wartosc_mat.HeaderText = "Wartośc";
                wartosc_mat.Name = "WartoscMat";
                wartosc_mat.Visible = true;
                wartosc_mat.Width = 55;
                wartosc_mat.DefaultCellStyle.Format = "0.0";
                wartosc_mat.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

                Nazwa.DataPropertyName = "element";
                Nazwa.HeaderText = "Nazwa";
                Nazwa.Width = 130;

                
                Material.DataPropertyName = "material";
                Material.HeaderText = "Materiał";
                Material.Name = "Material";
                Material.Width = 45;

                Wymiary.DataPropertyName = "abmessungen";
                Wymiary.HeaderText = "Wymiary";
                Wymiary.Name = "Wymiary";
                Wymiary.Width = 60;

                Producent.DataPropertyName = "liferant";
                Producent.HeaderText = "Producent";
                Producent.Name = "Producent";
                Producent.Width = 55;
                
                Numer.DataPropertyName = "art_numer";
                Numer.HeaderText = "Numer";
                Numer.Width = 50;

                
                Dlugosc.DataPropertyName = "dlugosc";
                Dlugosc.HeaderText = "Długośc";
                Dlugosc.Name = "Dlugosc";
                Dlugosc.Width = 50;
                Dlugosc.DefaultCellStyle.Format = "0.0";
                Dlugosc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

                Szerokosc.DataPropertyName = "szerokosc";
                Szerokosc.HeaderText = "Szerokosc";
                Szerokosc.Name = "Szerokosc";
                Szerokosc.Width = 50;
                Szerokosc.DefaultCellStyle.Format = "0.0";
                Szerokosc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

                IloscRysunkow.DataPropertyName = "il_rys";
                IloscRysunkow.HeaderText = "Rysunki";
                IloscRysunkow.Name = "IloscRysunkow";
                IloscRysunkow.Width = 50;
                IloscRysunkow.DefaultCellStyle.Format = "0";
                IloscRysunkow.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

                IloscPaleta.DataPropertyName = "il_paleta";
                IloscPaleta.HeaderText = "Paleta";
                IloscPaleta.Name = "IloscPaleta";
                IloscPaleta.Width = 50;
                IloscPaleta.DefaultCellStyle.Format = "0";
                IloscPaleta.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

                Znacznik.DataPropertyName = "znacznik_korpus_mdf";
                Znacznik.HeaderText = "Znacznik";
                Znacznik.Name = "Znacznik";
                Znacznik.Width = 50;

                // kolumny z ktorych pobieram dane
                this.dataGridViewBudowa.Columns.Add(IDe);
                this.dataGridViewBudowa.Columns.Add(IDk);

                this.dataGridViewBudowa.Columns.Add(ilosc_szt);
                this.dataGridViewBudowa.Columns.Add(Numer);
                this.dataGridViewBudowa.Columns.Add(Nazwa);
                this.dataGridViewBudowa.Columns.Add(cena_mat);
                this.dataGridViewBudowa.Columns.Add(wartosc_mat);

                this.dataGridViewBudowa.Columns.Add(Material);
                this.dataGridViewBudowa.Columns.Add(Wymiary);
                this.dataGridViewBudowa.Columns.Add(Producent);

                this.dataGridViewBudowa.Columns.Add(Dlugosc);
                this.dataGridViewBudowa.Columns.Add(Szerokosc);

                this.dataGridViewBudowa.Columns.Add(IloscRysunkow);
                this.dataGridViewBudowa.Columns.Add(IloscPaleta);
                this.dataGridViewBudowa.Columns.Add(Znacznik);
                
            }

            this.dataGridViewBudowa.Columns["Material"].Visible = (curentGMat == "AKCES") ? false : true;
            this.dataGridViewBudowa.Columns["Wymiary"].Visible = (curentGMat == "AKCES") ? true : false;
            this.dataGridViewBudowa.Columns["Producent"].Visible = (curentGMat == "AKCES") ? true : false;

            this.dataGridViewBudowa.Columns["CenaMat"].Visible = (curentGMat == "AKCES") ? true : false;
            this.dataGridViewBudowa.Columns["WartoscMat"].Visible = (curentGMat == "AKCES") ? true : false;

            this.dataGridViewBudowa.Columns["Dlugosc"].Visible = (curentGMat != "AKCES") ? true : false;
            this.dataGridViewBudowa.Columns["Szerokosc"].Visible = (curentGMat != "AKCES") ? true : false;
            
            this.dataGridViewBudowa.Columns["IloscRysunkow"].Visible = (curentGMat != "AKCES") ? true : false;
            this.dataGridViewBudowa.Columns["IloscPaleta"].Visible = (curentGMat != "AKCES") ? true : false;
            this.dataGridViewBudowa.Columns["Znacznik"].Visible = (curentGMat != "AKCES") ? true : false;
        }


        private void obliczWartoscElementu()
        {
            try
            {

            string ilosc = (this.textBoxIlosc.Text.Length != 0) ? this.textBoxIlosc.Text : "0";
            string cena = (this.textBoxCenaMat.Text.Length != 0) ? this.textBoxCenaMat.Text : "0";

            decimal wartosc = Convert.ToDecimal(ilosc) * Convert.ToDecimal(cena);
            this.textBoxWartoscMat.Text = wartosc.ToString("# ### ##0.00");
            }
            catch (Exception e) //Error
            {
                MessageBox.Show(e.Message, "Error - Błędna wartosc", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
             
        }

        private void buttonZapisz_Click(object sender, EventArgs e)
        {
            zapiszZmianyElementu();
            MessageBox.Show("Zapisano...");
        }

        private void zapiszZmianyElementu()
        {


            //textBoxCenaMat.DataBindings.Add(new Binding("Text", akcesoriaDataSet.Tables["elementy"], "cena_mat"));
            string queryString = "UPDATE elementy SET " +
                "element='" + textBoxElement.Text + "' " +
                ",material='" + comboBoxMaterial.SelectedValue.ToString() + "' " +
                ",ilosc_szt='" + textBoxIlosc.Text.Replace(",",".") + "'"+ 
                ",abmessungen='" + textBoxRozmiary.Text + "'" +
                ",liferant='" + textBoxProducent.Text + "'" +
                ",art_numer='" + textBoxArtNumer.Text + "'" +
                ",dlugosc='" + textBoxDlugosc.Text.Replace(",", ".") + "'" +
                ",szerokosc='" + textBoxSzerokosc.Text.Replace(",", ".") + "'" +
                ",il_rys='" + textBoxIloscRysunkow.Text.Replace(",", ".") + "'" +
                ",il_paleta='" + textBoxIloscPaleta.Text.Replace(",", ".") + "'" +
                ",znacznik_korpus_mdf='" + textBoxZnacznik.Text + "'" +
                "WHERE id_element='"+curentElement+"'";
            classConfig.exec(queryString,classConfig.sqlConnString);

            this.BindingContext[akcesoriaDataSet, "elementy"].Position += 1;
            this.BindingContext[akcesoriaDataSet, "elementy"].Position -= 1;
            this.dataGridViewBudowa.Refresh();
        }

        private void textBoxCenaMat_TextChanged(object sender, EventArgs e)
        {
            obliczWartoscElementu();
        }

        private void textBoxIlosc_TextChanged(object sender, EventArgs e)
        {
            obliczWartoscElementu();
        }





    








        private string fileCSV;		//full file name
		private string dirCSV;		//directory of file to import
		private string fileNevCSV;	//name (with extension) of file to import - field

		public string FileNevCSV	//name (with extension) of file to import - property
		{
			get { return fileNevCSV; }
			set { fileNevCSV = value; }
		}

		private string strFormat;	//CSV separator character
		private string strEncoding; //Encoding of CSV file

		private long rowCount = 0;	//row number of source file

        private DataSet csvDataSet;

		// Browses file with OpenFileDialog control

		private void btnFileOpen_Click(object sender, EventArgs e)
		{
			OpenFileDialog openFileDialogCSV = new OpenFileDialog();
			openFileDialogCSV.InitialDirectory = Application.ExecutablePath.ToString();
			openFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
			openFileDialogCSV.FilterIndex = 1;
			openFileDialogCSV.RestoreDirectory = true;

			if (openFileDialogCSV.ShowDialog() == DialogResult.OK)
			{
				this.txtFileToImport.Text = openFileDialogCSV.FileName.ToString();
			}

		}



		// Delimiter character selection
		private void Format()
		{
			try
			{

				if (rdbSemicolon.Checked)
				{
					strFormat = "Delimited(;)";
				}
				else if (rdbTab.Checked)
				{
					strFormat = "TabDelimited";
				}
				else if (rdbSeparatorOther.Checked)
				{
					strFormat = "Delimited(" + txtSeparatorOtherChar.Text.Trim() + ")";
				}
				else
				{
					strFormat = "Delimited(;)";
				}


			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, "Format");
			}
			finally
			{
			}
		}


		// Encoding selection
		private void Encoding()
		{
			try
			{

				if (rdbAnsi.Checked)
				{
					strEncoding = "ANSI";
				}
				else if (rdbUnicode.Checked)
				{
					strEncoding = "Unicode";
				}
				else if (rdbOEM.Checked)
				{
					strEncoding = "OEM";
				}
				else
				{
					strEncoding = "ANSI";
				}


			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, "Encoding");
			}
			finally
			{
			}
		}



		/* Schema.ini File (Text File Driver)

		When the Text driver is used, the format of the text file is determined by using a
		schema information file. The schema information file, which is always named Schema.ini
		and always kept in the same directory as the text data source, provides the IISAM 
		with information about the general format of the file, the column name and data type
		information, and a number of other data characteristics*/

		private void writeSchema()
		{
			try
			{
				FileStream fsOutput = new FileStream(this.dirCSV + "\\schema.ini", FileMode.Create, FileAccess.Write);
				StreamWriter srOutput = new StreamWriter(fsOutput);
				string s1, s2, s3, s4, s5;

				s1 = "[" + this.FileNevCSV + "]";
				s2 = "ColNameHeader=" + chkFirstRowColumnNames.Checked.ToString();
				s3 = "Format=" + this.strFormat;
				s4 = "MaxScanRows=25";
				s5 = "CharacterSet=" + this.strEncoding;

				srOutput.WriteLine(s1.ToString() + "\r\n" + s2.ToString() + "\r\n" + s3.ToString() + "\r\n" + s4.ToString() + "\r\n" + s5.ToString());
				srOutput.Close();
				fsOutput.Close();
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, "writeSchema");
			}
			finally
			{ }
		}

		/*
		 * Loads the csv file into a DataSet.
		 * 
		 * If the numberOfRows parameter is -1, it loads oll rows, otherwise it
		 * loads the first specified number of rows (for preview)
		 */

		public DataSet LoadCSV(int numberOfRows)
		{
            DataSet ds = new DataSet();
			try
			{
				// Creates and opens an ODBC connection
				string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
				string sql_select;
				OdbcConnection conn;
				conn = new OdbcConnection(strConnString.Trim());
				conn.Open();

				//Creates the select command text
				if (numberOfRows == -1)
				{
					sql_select = "select * from [" + this.FileNevCSV.Trim() + "]";
				}
				else
				{
					sql_select = "select top " + numberOfRows + " * from [" + this.FileNevCSV.Trim() + "]";
				}

				//Creates the data adapter
				OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);

               

				//Fills dataset with the records from CSV file
				obj_oledb_da.Fill(ds, "elementy");
				//closes the connection
				conn.Close();
			}
			catch (Exception e) //Error
			{
				MessageBox.Show(e.Message, "Error - LoadCSV",MessageBoxButtons.OK,MessageBoxIcon.Error);
			}
			return ds;
		}


        		/*
		 * Loads the orginal database into a DataSet.
		 * 
		 * 
		 */

		public DataSet LoadDataSet(string sql_select, string tableName)
		{
			DataSet ds = new DataSet();
			try
			{
				// Creates and opens an ODBC connection
				string strConnString = classConfig.sqlConnString;
				OdbcConnection conn;
				conn = new OdbcConnection(strConnString.Trim());
				conn.Open();

				//Creates the data adapter
				OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
				
				//Fills dataset with the records from CSV file
				obj_oledb_da.Fill(ds, tableName);
				
				//closes the connection
				conn.Close();
			}
			catch (Exception e) //Error
			{
				MessageBox.Show(e.Message, "Error - LoadCSV",MessageBoxButtons.OK,MessageBoxIcon.Error);
			}
			return ds;
		}





		// Checks if a file was given.
		private bool fileCheck()
		{
			if ((fileCSV == "") || (fileCSV == null) || (dirCSV == "") || (dirCSV == null) || (FileNevCSV == "") || (FileNevCSV == null))
			{
				MessageBox.Show("Select a CSV file to load first!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
			else
			{
				return true;
			}
		}




		private void btnPreview_Click(object sender, EventArgs e)
		{
			loadPreview();
            this.radioButtonImportujAkcesoria.Checked = (this.dataGridView_preView.Columns.Count == 8 ) ? true : false;
            this.radioButtonImportujElementy.Checked = (this.dataGridView_preView.Columns.Count == 17) ? true : false;
		}


        /* Loads the preview of CSV file in the DataGridView control.
         */

		private void loadPreview()
		{
			try
			{
				// select format, encoding, an write the schema file
				Format();
				Encoding();
				writeSchema();

				// loads the first 500 rows from CSV file, and fills the
				// DataGridView control.
                csvDataSet = LoadCSV(-1);

                //kolumnyAkcesoria();
                this.dataGridView_preView.DataSource = csvDataSet;
				this.dataGridView_preView.DataMember = "elementy";
			}
			catch (Exception e)
			{
				MessageBox.Show(e.Message, "Error - loadPreview", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}

		}

		private void btnSave_Click(object sender, EventArgs e)
		{
			SaveToDatabase_withDataSet();
		}


		/*
		 * Loads the CSV file to a dataset, and imports data
		 * to the database with SqlBulkCopy.
		 */

		private void SaveToDatabase_withDataSet()
		{
			try
			{
				if (fileCheck())
				{
					// select format, encoding, and write the schema file
					Format();
					Encoding();
					writeSchema();

					// loads all rows from from csv file
                    DataSet ds = csvDataSet;

					// gets the number of rows
					this.rowCount = ds.Tables[0].Rows.Count;

					// Makes a DataTableReader, which reads data from data set.
					// It is nececery for bulk copy operation.
					DataTableReader dtr = ds.Tables[0].CreateDataReader();

                    try
                    {
 
                        DataTable dtab = new DataTable();
                        
                        dtab = ds.Tables["elementy"];
                        
                        // importuje
                        if (dtab.Columns.Count != 8 && radioButtonImportujAkcesoria.Checked ) throw new System.ArgumentException("Niezgodna ilośc kolumn z plikiem akcesorii");
                        if (dtab.Columns.Count != 17 && radioButtonImportujElementy.Checked) throw new System.ArgumentException("Niezgodna ilośc kolumn z plikiem elementów");

                        int j = 1;
                        string one = classConfig.getOne("SELECT top 1 id_element FROM elementy ORDER BY convert(integer, id_element) desc", classConfig.sqlConnString);
                        int id_element_ = Convert.ToInt16(one);

                        foreach (DataRow row in dtab.Rows)
                        {
                            string [] oneRow = new string[] {"","","","","","","","","","","","","","","","",""};
                            int i = 0;
                            foreach (DataColumn col in dtab.Columns)
                            {
                                oneRow[i] = row[col.ToString()].ToString();
                                i++;
                            }
                            
                            if (radioButtonImportujAkcesoria.Checked)
                            {
                                // importuje akcesoria
                                id_element_++;
                                string query = "INSERT INTO elementy (id_element,id_komplet," +
                                "id_paczka,model,czesc,element,material,ilosc_szt,nazwa_pel," +
                                "grupa_mat,bezelchnung,abmessungen,liferant,art_nr_holtk,verwendung,art_nr_lieferant,art_numer) " +
                                "VALUES (" + id_element_ + "," + curentKomplet + "," + curentPaczka + ",'" + listBoxKomplety.GetItemText(listBoxKomplety.SelectedItem) +
                                "','" + this.nazwaProgramu.Text + "','" + oneRow[2] + "','AKCES'," + oneRow[0] +
                                ",'" + oneRow[2] + "','AKCES','" + oneRow[2] + "','" + oneRow[3] + "','" + oneRow[4] +
                                "','" + oneRow[6] + "','" + oneRow[7] + "','" + oneRow[6] + "','" + oneRow[6] + "');";

                                if (Convert.ToInt16(curentKomplet) <= 0) throw new System.ArgumentException("Nie wybrano kompletu do którego mają zostac zaimportowane dane");
                                if (Convert.ToInt16(curentPaczka) <= 0) throw new System.ArgumentException("Nie wybrano paczki w której mają zostac zaimportowane dane");

                                classConfig.exec(query,classConfig.sqlConnString);

                                // Writes the number of imported rows to the form
                                this.lblProgress.Text = "Imported: " + j.ToString() + "/" + this.rowCount.ToString() + " row(s)";
                                this.lblProgress.Refresh();
                                j++;
                            }

                            if (radioButtonImportujElementy.Checked)
                            {
                                // importuje elementy
                                id_element_++;
                                string query = "INSERT INTO elementy (id_element,id_komplet," +
                                "id_paczka,model,czesc,element,material,dlugosc,szerokosc,ilosc_szt,nazwa_pel," +
                                "grupa_mat,il_paleta,kol_gora,kol_dol,"+
                                "kant_d_1,kant_d_2,kant_k_1,kant_k_2,durch,ausges,sonstiges,art_nr_holtk,art_numer,"+
                                "grubosc,il_rys,znacznik_korpus_mdf) " +
                                "VALUES (" + 
                                id_element_ + "," + 
                                curentKomplet + "," + 
                                curentPaczka + ",'" + 
                                this.nazwaProgramu.Text + "','" + /*model*/
                                listBoxKomplety.GetItemText(listBoxKomplety.SelectedItem) + "','" + /*czesc*/
                                oneRow[3] + "','" + /*element*/
                                grupaMaterialowaPL(oneRow[4]) + oneRow[7].Replace(",", ".") + "'," + /*material*/
                                oneRow[5] + /*dlugosc*/
                                ",'" + oneRow[6] + /*szerokosc*/
                                "','" + oneRow[0] + /*ilosc_szt*/
                                "','" + oneRow[3] + /*nazwa_pel*/
                                "','" + grupaMaterialowaPL(oneRow[4]) + /*grupa_mat*/
                                "','" + ilosc_na_palete(oneRow[7]) + /*ilo_na_palete*/
                                "','" + oneRow[8] + /*kol_gora*/
                                "','" + oneRow[9] + /*kol_dol*/
                                "','" + ((oneRow[10] == "1") ? "-1" : "0") + /*kant_d_1*/
                                "','" + ((oneRow[11] == "1") ? "-1" : "0") + /*kant_d_2*/
                                "','" + ((oneRow[12] == "1") ? "-1" : "0") + /*kant_k_1*/
                                "','" + ((oneRow[13] == "1") ? "-1" : "0") + /*kant_k_2*/
                                "','" + oneRow[14] + /*durch*/
                                "','" + oneRow[15] + /*ausges*/
                                "','" + oneRow[16] + /*soustiges*/
                                "','" + oneRow[2] + /*art_nr_holtk*/
                                "','" + oneRow[2] + /*art_numer*/
                                "','" + oneRow[7] + /*grubosc*/
                                "','1"+ /*il_rys*/
                                "','" + okreslKorpusCzyMdf(oneRow[4]) + /*znacznik_korpus_mdf*/
                                "');";

                                if (Convert.ToInt16(curentKomplet) <= 0) throw new System.ArgumentException("Nie wybrano kompletu do którego mają zostac zaimportowane dane");
                                if (Convert.ToInt16(curentPaczka) <= 0) throw new System.ArgumentException("Nie wybrano paczki w której mają zostac zaimportowane dane");

                                classConfig.exec(query, classConfig.sqlConnString);

                                // Writes the number of imported rows to the form
                                this.lblProgress.Text = "Imported: " + j.ToString() + "/" + this.rowCount.ToString() + " row(s)";
                                this.lblProgress.Refresh();
                                j++;
                            }                                
                        }
                   }
                    catch (Exception e) //Error
                    {
                        MessageBox.Show(e.Message, "Error - Zapis do bazy", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

					// Notifies user
					MessageBox.Show("Zapis zakończony.");
				}
			}
			catch (Exception e)
			{
				MessageBox.Show(e.Message, "Error - Zapis do bazy nie powiódł się.", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}


        private static string ilosc_na_palete(string gr)
        {
            string il = "";
            switch(gr)
            {
                case "3,2":
                    il = "100";
                    break;
                case "3.2":
                    il = "100";
                    break;
                case "16":
                    il = "75";
                    break;
                case "18":
                    il = "65";
                    break;
                case "22":
                    il = "50";
                    break;
                case "32":
                    il = "35";
                    break;
            }
            return il;
        }

        private static string grupaMaterialowaPL(string mat)
        {
            string nazw = "";
            switch (mat)
            {
                case "FPY":
                    nazw = "PWL";
                    break;
                case "HF":
                    nazw = "HDF";
                    break;
                case "GLASS":
                    nazw = "SZKLO";
                    break;
                default:
                    nazw = mat;
                    break;
            }
            return nazw;
        }

        private static string okreslKorpusCzyMdf(string mat)
        {
            string nazw = "";
            switch (mat)
            {
                case "FPY":
                    nazw = "KORP";
                    break;
                case "PWL":
                    nazw = "KORP";
                    break;
                case "HF":
                    nazw = "KORP";
                    break;
                case "HDF":
                    nazw = "KORP";
                    break;
             }
            return nazw;
        }

  protected static void OnMergeFailed  (object sender, MergeFailedEventArgs args)
  {
    MessageBox.Show(args.Conflict.ToString());
  }
		/*
		 *  shows the progress of import operation
		 */

		private void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
		{
			this.lblProgress.Text = "Imported: " + e.RowsCopied.ToString() + "/" + this.rowCount.ToString() + " row(s)";
			this.lblProgress.Refresh();
		}


		private void btnSaveDirect_Click(object sender, EventArgs e)
        {

        }

		/*
		 * Imports data to the database with SqlBulkCopy.
		 * This method doesn't use a temporary dataset, it loads
		 * data immediately from the ODBC connection
		 */

		private void SaveToDatabaseDirectly()
		{
			try
			{
				if (fileCheck())
				{
					// select format, encoding, and write the schema file
					Format();
					Encoding();
					writeSchema();

					// Creates and opens an ODBC connection
					string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
					string sql_select;
					OdbcConnection conn;
					conn = new OdbcConnection(strConnString.Trim());
					conn.Open();

					//Counts the row number in csv file - with an sql query
					OdbcCommand commandRowCount = new OdbcCommand("SELECT COUNT(*) FROM [" + this.FileNevCSV.Trim() + "]", conn);
					this.rowCount = System.Convert.ToInt32(commandRowCount.ExecuteScalar());

					// Creates the ODBC command
					sql_select = "select * from [" + this.FileNevCSV.Trim() + "]";
					OdbcCommand commandSourceData = new OdbcCommand(sql_select, conn);

					// Makes on OdbcDataReader for reading data from CSV
					OdbcDataReader dataReader = commandSourceData.ExecuteReader();

					// Creates schema table. It gives column names for create table command.
					DataTable dt;
					dt = dataReader.GetSchemaTable();

					// You can view that schema table if you want:
					//this.dataGridView_preView.DataSource = dt;

					// Creates a new and empty table in the sql database
					//CreateTableInDatabase(dt, this.txtOwner.Text, this.txtTableName.Text, clssConnection.sqlConnString);

					// Copies all rows to the database from the data reader.
					using (SqlBulkCopy bc = new SqlBulkCopy("server=(local);database=Test_CSV_impex;Trusted_Connection=True"))
					{
						// Destination table with owner - this example doesn't
						// check the owner and table names!
						
                        //bc.DestinationTableName = "[" + this.txtOwner.Text + "].[" + this.txtTableName.Text + "]";

						// User notification with the SqlRowsCopied event
						bc.NotifyAfter = 100;
						bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

						// Starts the bulk copy.
						bc.WriteToServer(dataReader);

						// Closes the SqlBulkCopy instance
						bc.Close();
					}

					// Writes the number of imported rows to the form
					this.lblProgress.Text = "Imported: " + this.rowCount.ToString() + "/" + this.rowCount.ToString() + " row(s)";
					this.lblProgress.Refresh();
					
					// Notifies user
					MessageBox.Show("ready");
				}
			}
			catch (Exception e)
			{
				MessageBox.Show(e.Message, "Error - SaveToDatabaseDirectly", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}


		/*
		 * Generates the create table command using the schema table, and
		 * runs it in the sql database.
		 */

		private bool CreateTableInDatabase(DataTable dtSchemaTable, string tableOwner, string tableName, string connectionString)
		{
			try
			{

				// Generates the create table command.
				// The first column of schema table contains the column names.
				// The data type is nvarcher(4000) in all columns.

				string ctStr = "CREATE TABLE [" + tableOwner + "].[" + tableName + "](\r\n";
				for (int i = 0; i < dtSchemaTable.Rows.Count; i++)
				{
					ctStr += "  [" + dtSchemaTable.Rows[i][0].ToString() + "] [nvarchar](4000) NULL";
					if (i < dtSchemaTable.Rows.Count)
					{
						ctStr += ",";
					}
					ctStr += "\r\n";
				}
				ctStr += ")";

				// You can check the sql statement if you want:
				//MessageBox.Show(ctStr);


				// Runs the sql command to make the destination table.

                OdbcConnection conn = new OdbcConnection(connectionString);
                OdbcCommand command = conn.CreateCommand();
				command.CommandText = ctStr;
				conn.Open();
				command.ExecuteNonQuery();
				conn.Close();

				return true;

			}
			catch (Exception e)
			{
				MessageBox.Show(e.Message, "CreateTableInDatabase", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}
		}

		/*
		 * Makes file and directory information, and suggests a table name
		 */

		private void tbFile_TextChanged(object sender, EventArgs e)
		{
			// full file name
			this.fileCSV = this.txtFileToImport.Text;

			// creates a System.IO.FileInfo object to retrive information from selected file.
			System.IO.FileInfo fi = new System.IO.FileInfo(this.fileCSV);
			// retrives directory
			this.dirCSV = fi.DirectoryName.ToString();
			// retrives file name with extension
			this.FileNevCSV = fi.Name.ToString();

			// database table name
			//this.txtTableName.Text = fi.Name.Substring(0, fi.Name.Length - fi.Extension.Length).Replace(" ", "_");
		}

		private void txtSeparatorOtherChar_TextChanged(object sender, EventArgs e)
		{
			this.rdbSeparatorOther.Checked = true;
        }

        private void buttonUsunElement_Click(object sender, EventArgs e)
        {
            try
            {
                string query = "DELETE FROM elementy WHERE id_element = "+curentElement;
                classConfig.exec(query,classConfig.sqlConnString);
                this.kompletLoadBudowa();
            }
			catch (Exception x)
			{
				MessageBox.Show(x.Message, "Usuwanie elementu...", MessageBoxButtons.OK, MessageBoxIcon.Error);
				
			}
        }

        private void buttonEdytujKomplet_Click(object sender, EventArgs e)
        {
            formNowyEdytujKomplet formNowyEdytujKomplet_ = new formNowyEdytujKomplet(listBoxKomplety.SelectedValue.ToString(), curentWyrob, nazwaProgramu.Text);
            DialogResult dialogResult = formNowyEdytujKomplet_.ShowDialog();
            kompletyLoad();
        }

        private void buttonDodajKomplet_Click(object sender, EventArgs e)
        {
            formNowyEdytujKomplet formNowyEdytujKomplet_ = new formNowyEdytujKomplet(null,curentWyrob,nazwaProgramu.Text);
            DialogResult dialogResult = formNowyEdytujKomplet_.ShowDialog();
            kompletyLoad();
        }

        private void buttonExportToExcel_Click(object sender, EventArgs e)
        {
            FolderBrowser myBrowser = new FolderBrowser();
            myBrowser.Title = "Wskaż folder do zapisania pliku:";
            myBrowser.Flags = BrowseFlags.BIF_NEWDIALOGSTYLE |
                              BrowseFlags.BIF_STATUSTEXT |
                              BrowseFlags.BIF_EDITBOX; ;
           
            DialogResult res = myBrowser.ShowDialog();
            if (res == DialogResult.OK)
            {
                string myPath = myBrowser.DirectoryPath;
                
                ExportDataTableToExcel(myPath);
            }            
        }

        private void ExportDataTableToExcel(string activeDir)
        {
            ExcelFile ef = new ExcelFile();
            ExcelWorksheet ws = ef.Worksheets.Add("DataSheet");
            ws.InsertDataTable(budowaDataTable, "A1", true);

            string fileName = curentGMat+"_"+ listBoxKomplety.GetItemText(listBoxKomplety.SelectedItem).Replace(@"\", "_") + ".xls";

            //Create a new subfolder under the current active folder
           // string newPath = System.IO.Path.Combine(activeDir, this.nazwaProgramu.Text.Replace(@"\", "_"));

            // Create the subfolder
            //System.IO.Directory.CreateDirectory(newPath);
            
            ef.SaveXls(fileName);
            // Uncomment if you want to export in XLSX.
            //string fileName = "HelloWorldCS.xlsx";
            //excelFile.SaveXlsx(fileName);

            TryToDisplayGeneratedFile(fileName);
        }

        static void TryToDisplayGeneratedFile(string fileName)
        {
            try
            {
                System.Diagnostics.Process.Start(fileName);
            }
            catch (Exception)
            {
                Console.WriteLine(fileName + " created in application folder.");
            }
        }

        private void listBoxListaRaportow_Click(object sender, EventArgs e)
        {
            string curentRaport = listBoxListaRaportow.SelectedItem.ToString();
            formWydrukiKomplet formWydrukiKomplet_ = new formWydrukiKomplet(curentRaport);
            DialogResult dialogResult = formWydrukiKomplet_.ShowDialog();
        }


        private void buttonDodajPaczke_Click(object sender, EventArgs e)
        {
            string nazwa_kompletu = listBoxKomplety.GetItemText(listBoxKomplety.SelectedItem);
            formNowyEdytujPaczka formNowyEdytujPaczka_ = new formNowyEdytujPaczka(null, curentKomplet, nazwa_kompletu);
            DialogResult dialogResult = formNowyEdytujPaczka_.ShowDialog();
            paczkiLoad();
        }

        private void buttonEdytujPaczke_Click(object sender, EventArgs e)
        {
            formNowyEdytujPaczka formNowyEdytujPaczka_ = new formNowyEdytujPaczka(curentPaczka,curentKomplet,"");
            DialogResult dialogResult = formNowyEdytujPaczka_.ShowDialog();
            paczkiLoad();
        }


        private void loadKartyTechniczne()
        {
            cKTech.Program = curentWyrob;
            cKTech.Komplet = curentKomplet;
            cKTech.sprawdzIstnienieFolderuKompletu();
            cKTech.Element = null;
            dsElem = cKTech.ListaElementow();
            DataRow popKT = cKTech.PoprawnoscListyKartTechnicznych();
            this.textBoxPoprawnoscKart.Text = (popKT == null) ? "Karty nie zweryfikowane!" : popKT["korp"].ToString();
            if (dsElem != null)
            {
                cKTech.obslugaKolumnDataGrid(dataGridViewElementyVsKartyTech);
                dsElem = cKTech.sprawdzPlikiGifDlaElementow(dsElem);

                if(this.textBoxPdfPath.Text != null && this.textBoxPdfPath.Text != "")
                    dsElem = cKTech.sprawdzPlikiPdfDlaElementow(dsElem, this.textBoxPdfPath.Text);

                this.dataGridViewElementyVsKartyTech.DataSource = null;
                if (dsElem != null) this.dataGridViewElementyVsKartyTech.DataSource = dsElem.Tables[0];                
            }
            else
            {
                this.dataGridViewElementyVsKartyTech.DataSource = null;
            }
        }

        private void dataGridViewElementyVsKartyTech_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                
                int row = e.RowIndex;

                // Wczytaj plik PDF
                if (e.RowIndex >= 0 && e.RowIndex == row && e.ColumnIndex == this.dataGridViewElementyVsKartyTech.Columns["Wczytaj"].Index)
                {
                    // Ignoruj jeśli kolumna plik_pdf jest juz wypełniona
                    if (dataGridViewElementyVsKartyTech[4, e.RowIndex].Value.ToString() != "") return;

                    // Retrieve the task ID.
                    //string taskID =  dataGridViewElementyVsKartyTech[0, e.RowIndex].Value.ToString();

                    string newFilePDF = cKTech.wkazPlikPdfKartyTechnicznej();
                    if (newFilePDF == null) return;
                    System.IO.FileInfo fi = new System.IO.FileInfo(newFilePDF);
                    // retrives directory
                    //this.dirCSV = fi.DirectoryName.ToString();
                    // retrives file name with extension
                    string PdfFileName = fi.Name.ToString();
                    if (PdfFileName != "" && PdfFileName.ToLower() == dataGridViewElementyVsKartyTech[1, e.RowIndex].Value.ToString().ToLower() + ".pdf")
                    {
                        dataGridViewElementyVsKartyTech[4, e.RowIndex].Value = newFilePDF;
                    }
                    else if (PdfFileName != "")
                    {
                        MessageBox.Show("Wybrałeś niewłaściwy plik PDF.");
                        dataGridViewElementyVsKartyTech[4, e.RowIndex].Value = null;
                    }
                    return;
                }

                // konwertuj plik PDF
                if (e.RowIndex >= 0 && e.RowIndex == row && e.ColumnIndex == this.dataGridViewElementyVsKartyTech.Columns["Konwertuj"].Index)
                {
                    // sprawdz czy wskazano plik PDF
                    if (dataGridViewElementyVsKartyTech[4, e.RowIndex].Value.ToString() != ""
                        && System.IO.File.Exists(dataGridViewElementyVsKartyTech[4, e.RowIndex].Value.ToString()))
                        
                    {
                        string rootDir = classConfig.getConfSettingsForKey("kompletySciezka");
                        cKTech.konwertujPlikPdfToGif(dataGridViewElementyVsKartyTech[4, e.RowIndex].Value.ToString(), rootDir + curentKomplet + @"\" + dataGridViewElementyVsKartyTech[0, e.RowIndex].Value.ToString() + "-1.gif");
                        dataGridViewElementyVsKartyTech[3, e.RowIndex].Value = rootDir + curentKomplet + @"\" + dataGridViewElementyVsKartyTech[0, e.RowIndex].Value.ToString() + "-1.gif";
                        MessageBox.Show("Konwersja zakończona ppomyślnie","Konwersja...", MessageBoxButtons.OK);
                    }
                    
                }

                // open gif
                if (e.RowIndex >= 0 && e.RowIndex == row && e.ColumnIndex == this.dataGridViewElementyVsKartyTech.Columns["plik_gif"].Index)
                {
                    DialogResult dr = MessageBox.Show("Czy otwożyc plik " + dataGridViewElementyVsKartyTech[3, e.RowIndex].Value.ToString(),"Otwieranie...",MessageBoxButtons.OKCancel,MessageBoxIcon.Question,MessageBoxDefaultButton.Button1);
                    if (dr.ToString() == "OK")
                    {
                        if (dataGridViewElementyVsKartyTech[3, e.RowIndex].Value.ToString() != ""
                            && System.IO.File.Exists(dataGridViewElementyVsKartyTech[3, e.RowIndex].Value.ToString()))
                            System.Diagnostics.Process.Start(dataGridViewElementyVsKartyTech[3, e.RowIndex].Value.ToString());
                    }
                    return;
                }

                // open pdf
                if (e.RowIndex >= 0 && e.RowIndex == row && e.ColumnIndex == this.dataGridViewElementyVsKartyTech.Columns["plik_pdf"].Index)
                {
                    if (dataGridViewElementyVsKartyTech[4, e.RowIndex].Value.ToString() != ""
                        && System.IO.File.Exists(dataGridViewElementyVsKartyTech[4, e.RowIndex].Value.ToString()) )
                        System.Diagnostics.Process.Start(dataGridViewElementyVsKartyTech[4, e.RowIndex].Value.ToString());
                }

                // wstaw pusty plik www.gif
                if (e.RowIndex >= 0 && e.RowIndex == row && e.ColumnIndex == this.dataGridViewElementyVsKartyTech.Columns["wstawPusty"].Index)
                {
                    string plikWycinacWgWymiaru = classConfig.getConfSettingsForKey("plikWycWgWym");
                    if (System.IO.File.Exists(plikWycinacWgWymiaru))
                    {
                        // get file  name
                        string fileName = System.IO.Path.GetFileName(plikWycinacWgWymiaru);
                        // get destination folder
                        string rootDir = classConfig.getConfSettingsForKey("kompletySciezka");
                        //set destinanion file
                        string destFile = System.IO.Path.Combine(rootDir + curentKomplet, dataGridViewElementyVsKartyTech[0, e.RowIndex].Value.ToString() + "-1.gif");
                        // copu file
                        System.IO.File.Copy(plikWycinacWgWymiaru, destFile, true);
                        // schow file in datagrid
                        dataGridViewElementyVsKartyTech[3, e.RowIndex].Value = rootDir + curentKomplet + @"\" + dataGridViewElementyVsKartyTech[0, e.RowIndex].Value.ToString() + "-1.gif";
                    }
                }
              }
            catch (Exception x)
            {
                MessageBox.Show(x.Message, "Wczytywanie pliku PDF...", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
        }

        private void buttonOpenFolderWidthPDF_Click(object sender, EventArgs e)
        {
            this.textBoxPdfPath.Text = cKTech.okreslSciezkeDlaKartTechnicznych("Wskaż folder z Kartami technicznymi...");
            
        }

        private void textBoxPdfPath_TextChanged(object sender, EventArgs e)
        {
            if (this.textBoxPdfPath.Text != null && this.textBoxPdfPath.Text != "") 
                dsElem = cKTech.sprawdzPlikiPdfDlaElementow(dsElem, this.textBoxPdfPath.Text);

        }

        private void buttonWeryfikujStatusKT_Click(object sender, EventArgs e)
        {
            string wynikWeryfikacji = cKTech.weryfikujKartyTechniczne(dsElem);
            this.textBoxPoprawnoscKart.Text = wynikWeryfikacji;
        }

        private void textBoxIdProgram_TextChanged(object sender, EventArgs e)
        {
            kolorystykaPolID();
        }

        private void kolorystykaPolID()
        {
            if (this.textBoxIdProgram.Text == "" && this.textBoxIdKomplet.Text == "" && this.textBoxIdPaczki.Text == "" || this.textBoxIdProgram.Text == "" && this.textBoxIdKomplet.Text == "" && this.textBoxIdPaczki.Text == "0")
            {
                this.textBoxIdProgram.BackColor = SystemColors.Control;
                this.textBoxIdKomplet.BackColor = SystemColors.Control;
                this.textBoxIdPaczki.BackColor = SystemColors.Control;
            }
            if (this.textBoxIdProgram.Text != "" && this.textBoxIdKomplet.Text == "" && this.textBoxIdPaczki.Text == "" || this.textBoxIdProgram.Text != "" && this.textBoxIdKomplet.Text == "" && this.textBoxIdPaczki.Text == "0")
            {
                this.textBoxIdProgram.BackColor = System.Drawing.Color.Red;
                this.textBoxIdKomplet.BackColor = SystemColors.Control;
                this.textBoxIdPaczki.BackColor = SystemColors.Control;
            }
            if (this.textBoxIdProgram.Text != "" && this.textBoxIdKomplet.Text != "" && this.textBoxIdPaczki.Text == "" || this.textBoxIdProgram.Text != "" && this.textBoxIdKomplet.Text != "" && this.textBoxIdPaczki.Text == "0")
            {
                this.textBoxIdProgram.BackColor = System.Drawing.Color.Yellow;
                this.textBoxIdKomplet.BackColor = System.Drawing.Color.Yellow;
                this.textBoxIdPaczki.BackColor = SystemColors.Control;
            }

            if (this.textBoxIdProgram.Text != "" && this.textBoxIdKomplet.Text != "" && this.textBoxIdPaczki.Text != "" && this.textBoxIdPaczki.Text != "0")
            {
                this.textBoxIdProgram.BackColor = System.Drawing.Color.Green;
                this.textBoxIdKomplet.BackColor = System.Drawing.Color.Green;
                this.textBoxIdPaczki.BackColor = System.Drawing.Color.Green;
            }


        }

        private void textBoxIdKomplet_TextChanged(object sender, EventArgs e)
        {
            kolorystykaPolID();
        }

        private void textBoxIdPaczki_TextChanged(object sender, EventArgs e)
        {
            kolorystykaPolID();
        }

        private void buttonNowyElement_Click(object sender, EventArgs e)
        {

        }

        private void zapiszNowyElement()
        {
            try
            {
                string new_id_element_ = classConfig.getOne("SELECT top 1 id_element FROM elementy ORDER BY convert(integer, id_element) desc", classConfig.sqlConnString);
                int new_id_element = Convert.ToInt16(new_id_element_);
                new_id_element++;

                if (curentGMat == "RAZEM" || curentGMat == null) throw new System.ArgumentException("Nie wybrano grupy materiałowej");
                if (Convert.ToInt16(curentKomplet) <= 0) throw new System.ArgumentException("Nie wybrano kompletu do którego mają zostac zaimportowane dane");
                if (Convert.ToInt16(curentPaczka) <= 0) throw new System.ArgumentException("Nie wybrano paczki w której mają zostac zaimportowane dane");
                
                if (curentGMat == "AKCES")
                {
                    // dodaj akces
                    string query = "INSERT INTO elementy (id_element,id_komplet," +
                    "id_paczka,model,czesc,element,material,ilosc_szt,nazwa_pel," +
                    "grupa_mat,bezelchnung,abmessungen,liferant,art_nr_holtk,verwendung,art_nr_lieferant,art_numer) " +
                    "VALUES (" +
                    new_id_element + "," + /*id_element*/
                    curentKomplet + "," + /*id_komplet*/
                    curentPaczka + ",'" + /*id_paczka*/
                    listBoxKomplety.GetItemText(listBoxKomplety.SelectedItem) + "','" + /*model*/
                    this.nazwaProgramu.Text + "','" + /*czesc*/
                    textBoxElement.Text + "','AKCES','" +  /*element*/ /*material*/
                    textBoxIlosc.Text + "','" + /*ilosc_szt*/
                    textBoxElement.Text + "','AKCES','" + /*nazwa_pel*/ /*grupa_mat*/
                    textBoxElement.Text + "','" + /*bezelchnung*/
                    textBoxRozmiary.Text + "','" + /*abmessungen*/
                    textBoxProducent.Text + "','" + /*liferant*/
                    textBoxArtNumer.Text + "','" + /*art_nr_holtk*/
                    "','" + /*verwendung*/
                    "','" + /*art_nr_lieferant*/
                     textBoxArtNumer.Text + "');"; /*art_numer*/
                    MessageBox.Show(query);
                    //classConfig.exec(query, classConfig.sqlConnString);

                }

                if (curentGMat != "AKCES")
                {

                    switch(curentGMat)
                    {
                        case "PWL":
                            // dodaj elem

                            string query = "INSERT INTO elementy (id_element,id_komplet," +
                            "id_paczka,model,czesc,element,material,dlugosc,szerokosc,ilosc_szt,nazwa_pel," +
                            "grupa_mat,il_paleta,kol_gora,kol_dol," +
                            "kant_d_1,kant_d_2,kant_k_1,kant_k_2,durch,ausges,sonstiges,art_nr_holtk,art_numer," +
                            "grubosc,il_rys,znacznik_korpus_mdf) " +
                            "VALUES (" +
                            new_id_element + "," +
                            curentKomplet + "," +
                            curentPaczka + ",'" +
                            this.nazwaProgramu.Text + "','" + /*model*/
                            listBoxKomplety.GetItemText(listBoxKomplety.SelectedItem) + "','" + /*czesc*/
                            textBoxElement.Text + "','" + /*element*/
                            curentGMat + "'," + /*material*/
                            textBoxDlugosc.Text + /*dlugosc*/
                            ",'" + textBoxSzerokosc.Text + /*szerokosc*/
                            "','" + textBoxIlosc.Text + /*ilosc_szt*/
                            "','" + textBoxElement.Text + /*nazwa_pel*/
                            "','" + curentGMat + /*grupa_mat*/
                            "','" + textBoxIloscPaleta.Text + /*ilo_na_palete*/
                            "','" + textBoxDekorOben.Text + /*kol_gora*/
                            "','" + textBoxDekorUnten.Text + /*kol_dol*/
                            "','" + ((checkBoxKanteDlugosc1.Checked) ? "-1" : "0") + /*kant_d_1*/
                            "','" + ((checkBoxKanteDlugosc2.Checked) ? "-1" : "0") + /*kant_d_2*/
                            "','" + ((checkBoxKanteKrotki1.Checked) ? "-1" : "0") + /*kant_k_1*/
                            "','" + ((checkBoxKanteKrotki2.Checked) ? "-1" : "0") + /*kant_k_2*/
                            "','" + ((checkBoxNuteFalzDurch.Checked) ? "-1" : "0") + /*durch*/
                            "','" + ((checkBoxNuteFalzAusges.Checked) ? "-1" : "0") + /*ausges*/
                            "','" + listBoxUwagi.Text + /*soustiges*/
                            "','" + textBoxArtNumer.Text + /*art_nr_holtk*/
                            "','" + textBoxArtNumer.Text + /*art_numer*/
                            "','" + textBoxGrubosc.Text + /*grubosc*/
                            "','1" + /*il_rys*/
                            "','" + "KORP" + /*znacznik_korpus_mdf*/
                            "');";
                            //classConfig.exec(query, classConfig.sqlConnString);
                            MessageBox.Show(query);
                        break;

                    }
                }
            }
            catch (Exception x)
            {
                MessageBox.Show(x.Message, "Dodanie nowego elementu...", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
        }
    }
}
